#!/usr/bin/env python
# coding=UTF-8
import MySQLdb
import MySQLdb.cursors
from DBUtils.PooledDB import PooledDB
import logging
import time

import config

pool = PooledDB(MySQLdb, 20, 20, 0, 500, **{'host':config.DB_HOST, 'port':config.DB_PORT, 'user':config.DB_USER, 'passwd':config.DB_PWD, 'db': config.DB_NAME, 'charset':'utf8', 'cursorclass':MySQLdb.cursors.DictCursor})

class BaseDB(object):
    def __init__(self, conn=None, slave=False, bid=''):
        if conn:
            self.conn = conn
        else:
            self.conn = pool.connection()
        self.cur = self.conn.cursor()
        self.res = True

    def __del__(self):
        self.finish()
        
    def execute(self, sql, param=None):
        try:
            if 'select' not in sql.lower()[:7]:
                logging.info(sql)
            else:
                logging.debug(sql)
            if not param:
                return self.cur.execute(sql)
            else:
                return self.cur.execute(sql, param)
        except:
            self.res = False
            logging.error(u"DB error: %s" % sql, exc_info=True)
    
    def get(self, sql, param=None):
        self.execute(sql, param)
        return self.cur.fetchone()

    def query(self, sql, param=None):
        self.execute(sql, param)
        return self.cur.fetchall()

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()
        
    def finish(self):
        if self.res:
            self.conn.commit()
        else:
            self.conn.rollback()
        if self.cur:
            self.cur.close()
        if self.conn:
            self.conn.close()
      
class Game_DB(BaseDB):
    def get_conf(self, bid):
        sql = "select param, value from t_conf where bid='%s'" % bid
        return self.query(sql)
    
    def get_game_des(self):
        sql = "select * from t_game_des"
        return self.query(sql)
    
    def user_info(self, bid, uid):
        sql = "select * from t_user where bid='%s' and uid=%s" % (bid, uid)
        return self.get(sql)
    
    def get_wx_user(self, bid, openid):
        sql = "select * from t_user where bid='%s' and openid='%s'" % (bid, openid)
        return self.get(sql)
    
    def get_dev_user(self, bid, devid):
        sql = "select * from t_user where bid='%s' and devid='%s'" % (bid, devid)
        return self.get(sql)
    
    def add_user(self, bid, uid, pwd, openid, head, name, sex, cp, cv, card, pid, devid):
        sql = "insert into t_user(bid, uid, pwd, md5pwd, openid, head, name, sex, cp, cv, card, pid, devid)values('%s',%s,'%s',MD5('%s'),'%s','%s','%s',%s,'%s','%s',%s,%s,'%s')" % (bid, uid, pwd, pwd, openid, head, name, sex, cp, cv, card, pid, devid)
        return self.execute(sql)
    
    def get_max_uid(self, bid):
        sql = "select max(uid) as uid from t_user where bid='%s'" % bid
        return self.get(sql) 
    
    def get_joined_room(self, bid, uid):
        sql = "select a.admin, b.* from t_room a,t_room_player b where a.bid='%s' and a.flag='y' and b.uid=%s and a.rid=b.rid" % (bid, uid)
        return self.get(sql) 
    
    def get_room(self, rid, bid):
        sql = "select * from t_room where rid=%s and bid='%s'" % (rid, bid)
        return self.get(sql) 
    
    def get_free_seat(self, rid):
        sql = "select * from t_room_player where rid=%s and (uid is null or uid=0) limit 1" % rid
        return self.get(sql) 
    
    def join_room(self, rid, seat, uid, status, online, ip, gps_lat, gps_lon):
        sql = "update t_room_player set uid=%s,status='%s',online='%s',ip='%s',gps_lat='%s',gps_lon='%s' where rid=%s and `seat`=%s and (uid is null or uid=0) limit 1" % (uid, status, online, ip, gps_lat, gps_lon, rid, seat)
        return self.execute(sql)

    def return_room(self, rid, seat, uid, status, online, ip, gps_lat, gps_lon):
        #sql = "update t_room_player set status='%s',online='%s',ip='%s',gps_lat='%s',gps_lon='%s' where rid=%s and `seat`=%s and uid=%s limit 1" % (status, online, ip, gps_lat, gps_lon, rid, seat, uid)
        sql = "update t_room_player set online='%s',ip='%s',gps_lat='%s',gps_lon='%s' where rid=%s and `seat`=%s and uid=%s limit 1" % (online, ip, gps_lat, gps_lon, rid, seat, uid)
        return self.execute(sql)
    
    def quit_room(self, rid, uid):
        sql = "update t_room_player set uid=NULL,status=0,online='n',ip='',gps_lat='',gps_lon='' where rid=%s and uid=%s" % (rid, uid)
        return self.execute(sql)

    def admin_quit_room(self, rid, uid):
        sql = "update t_room_player set status=0,online='n' where rid=%s and uid=%s" % (rid, uid)
        return self.execute(sql)
    
    def query_room_players(self, rid, bid):
        sql = "select a.uid,a.head,a.name,a.sex,b.ip,b.status,b.online,b.seat,b.gps_lat,b.gps_lon,b.score from t_user a, t_room_player b where b.rid=%s and a.uid=b.uid and a.bid='%s'" % (rid, bid)
        return self.query(sql)
    
    def dismiss_room(self, rid, bid, uid):
        sql = "update t_room set flag='n' where rid=%s and bid='%s' and admin=%s" % (rid, bid, uid)
        return self.execute(sql)
    
    def room_player_cnt(self, rid, status=None):
        sql = "select count(1) as player from t_room_player where rid=%s" % rid
        if status:
            sql += " and status=%s" % status
        else:
            sql += " and uid>0"
        return self.get(sql)
    
    def add_room(self, bid, game_type, admin, all_round, card, player):
        sql = "insert into t_room(bid, game_type, admin, all_round, card, player)values('%s','%s', %s,%s,%s,%s)" % (bid, game_type, admin, all_round, card, player)
        i = self.execute(sql)
        return (i, int(self.cur.lastrowid))
    
    def add_player(self, rid, uid, status, online, ip, gps_lat, gps_lon, seat):
        sql = "insert into t_room_player(rid, uid, status, online, ip, gps_lat, gps_lon, seat)values(%s,%s,%s,'%s','%s','%s','%s',%s)" % (rid, uid, status, online, ip, gps_lat, gps_lon, seat)
        return self.execute(sql)
    
    def add_feedback(self,bid,uid,content):
        sql = "insert into t_feedback(bid,uid,content)values(%s,%s,%s)" % (bid, uid, content)
        return self.execute(sql)
    
    def add_room_rule(self, rid, param, value):
        sql = "insert into t_room_rule(rid,param,value)values(%s,'%s','%s')" % (rid, param, value)
        return self.execute(sql)
    
    def room_rule(self, rid):
        sql = "select * from t_room_rule where rid=%s" % rid
        return self.query(sql)
    